using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

namespace umlungu.engineX {
	/// <summary>
	/// umlungu EngineX class. 
	/// base utility class for accessing Sql database.
	/// </summary>
	public class x_SqlData {
		/// <summary>Database connection string</summary>
		protected string _dsn;
		
		/// <overloads>Constructor</overloads>
		/// <summary>Default constructor</summary>
		public x_SqlData() {	}
		/// <summary>Constructor with DSN</summary>
		/// <param name="dsn">Database connection string</param>
		protected x_SqlData(string dsn) {
			_dsn = dsn;
		}
		/// <overloads>Adds an integer output parameter to a stored procedure call.</overloads>
		/// <summary>
		/// General databases
		/// </summary>
		/// <param name="parcmd">SQL statement or stored procedure (IDbCommand)</param>
		/// <param name="parname">SQL parameter name</param>
		protected SqlParameter _addSqlPar(IDbCommand parcmd, string parname) {
			return _addSqlPar((SqlCommand) parcmd, parname);
		}
		/// <summary>
		/// MsSQL databases
		/// </summary>
		/// <param name="parcmd">SQL statement or stored procedure (IDbCommand)</param>
		/// <param name="parname">SQL parameter name</param>
		protected SqlParameter _addSqlPar(SqlCommand parcmd, string parname) {
			try {
				SqlParameter par = new SqlParameter(parname, SqlDbType.Int);
				par.Direction = ParameterDirection.Output;
				parcmd.Parameters.Add(par);
				return(par);
			} catch {
				return(null);
			}
		}
		/// <overloads>Adds a string output parameter to a stored procedure call.</overloads>
		/// <summary>
		/// General databases
		/// </summary>
		/// <param name="parcmd">SQL statement or stored procedure (IDbCommand)</param>
		/// <param name="parname">SQL parameter name</param>
		/// <param name="parlen">SQL parameter length</param>
		protected SqlParameter _addSqlPar(IDbCommand parcmd, string parname, int parlen) {
			return _addSqlPar((SqlCommand) parcmd, parname, parlen);
		}
		/// <summary>
		/// MsSQL databases
		/// </summary>
		/// <param name="parcmd">SQL statement or stored procedure (SqlCommand)</param>
		/// <param name="parname">SQL parameter name</param>
		/// <param name="parlen">SQL parameter length</param>
		protected SqlParameter _addSqlPar(SqlCommand parcmd, string parname, int parlen) {
			try {
				SqlParameter par = new SqlParameter(parname, SqlDbType.VarChar, parlen);
				par.Direction = ParameterDirection.Output;
				parcmd.Parameters.Add(par);
				return(par);
			} catch {
				return(null);
			}
		}
		/// <overloads>Adds an integer parameter to a stored procedure call.</overloads>
		/// <summary>
		/// General databases
		/// </summary>
		/// <param name="parcmd">SQL statement or stored procedure (IDbCommand)</param>
		/// <param name="parval">SQL parameter value</param>
		/// <param name="parname">SQL parameter name</param>
		protected SqlParameter _addSqlPar(IDbCommand parcmd, int parval, string parname) {
			return _addSqlPar((SqlCommand) parcmd, parval, parname);
		}
		/// <summary>
		/// MsSQL databases
		/// </summary>
		/// <param name="parcmd">SQL statement or stored procedure (SqlCommand)</param>
		/// <param name="parval">SQL parameter value</param>
		/// <param name="parname">SQL parameter name</param>
		protected SqlParameter _addSqlPar(SqlCommand parcmd, int parval, string parname) {
			try {
				SqlParameter par = new SqlParameter(parname, SqlDbType.Int);
				par.Value = parval;
				parcmd.Parameters.Add(par);
				return(par);
			} catch {
				return(null);
			}
		}
		/// <overloads>Adds an string parameter to a stored procedure call.</overloads>
		/// <summary>
		/// General databases
		/// </summary>
		/// <param name="parcmd">SQL statement or stored procedure (IDbCommand)</param>
		/// <param name="parval">SQL parameter value</param>
		/// <param name="parname">SQL parameter name</param>
		/// <param name="parlen">SQL parameter length</param>
		protected SqlParameter _addSqlPar(IDbCommand parcmd, string parval, string parname, int parlen) {
			return _addSqlPar((SqlCommand) parcmd, parval, parname, parlen);
		}
		/// <summary>
		/// MsSQL databases
		/// </summary>
		/// <param name="parcmd">SQL statement or stored procedure (SqlCommand)</param>
		/// <param name="parval">SQL parameter value</param>
		/// <param name="parname">SQL parameter name</param>
		/// <param name="parlen">SQL parameter length</param>
		protected SqlParameter _addSqlPar(SqlCommand parcmd, string parval, string parname, int parlen) {
			try {
				SqlParameter par = new SqlParameter(parname, SqlDbType.VarChar, parlen);
				par.Value = parval;
				parcmd.Parameters.Add(par);
				return(par);
			} catch {
				return(null);
			}
		}
		/// <overloads>Adds an uid parameter to a stored procedure call.</overloads>
		/// <summary>
		/// General databases
		/// </summary>
		/// <param name="parcmd">SQL statement or stored procedure (IDbCommand)</param>
		/// <param name="parval">SQL parameter value</param>
		/// <param name="parname">SQL parameter name</param>
		protected SqlParameter _addSqlUid(IDbCommand parcmd, string parval, string parname) {
			return _addSqlUid((SqlCommand) parcmd, parval, parname);
		}
		/// <summary>
		/// MsSQL databases
		/// </summary>
		/// <param name="parcmd">SQL statement or stored procedure (SqlCommand)</param>
		/// <param name="parval">SQL parameter value</param>
		/// <param name="parname">SQL parameter name</param>
		protected SqlParameter _addSqlUid(SqlCommand parcmd, string parval, string parname) {
			try {
				SqlParameter par = new SqlParameter(parname, SqlDbType.VarChar, 65);
				par.Value = parval;
				parcmd.Parameters.Add(par);
				return(par);
			} catch {
				return(null);
			}
		}
		/// <summary>
		/// Adds an attribute to a node of a document from a field in a recordset
		/// </summary>
		protected bool _addAttribute(XmlDocument xmlThis, XmlNode xnThis, SqlDataReader rdrThis, string attname, string fldname) {
			try {
				XmlAttribute xaThis = xmlThis.CreateAttribute(attname);
				if(!(rdrThis.IsDBNull(rdrThis.GetOrdinal(fldname))))
					xaThis.InnerText = rdrThis.GetSqlValue(rdrThis.GetOrdinal(fldname)).ToString();
				xnThis.Attributes.Append(xaThis);
				return(true);
			} catch {
				return(false);
			}
		}
		/// <summary>
		/// Adds an attribute to a node of a document
		/// </summary>
		protected bool _addAttribute(XmlDocument xmlThis, XmlNode xnThis, string attname, string attval) {
			try {
				XmlAttribute xaThis = xmlThis.CreateAttribute(attname);
				xaThis.InnerText = attval;
				xnThis.Attributes.Append(xaThis);
				return(true);
			} catch {
				return(false);
			}
		}
		/// <summary>
		/// Adds an element to a node of a document
		/// </summary>
		protected bool _addElement(XmlDocument xmlThis, XmlNode xnThis, string elname, string elval) {
			try {
				XmlElement xeThis = xmlThis.CreateElement(elname);
				xeThis.InnerText = elval;
				xnThis.AppendChild(xeThis);
				return(true);
			} catch {
				return(false);
			}
		}
		/// <summary>
		/// Adds an element and its attribute to a node of a document
		/// </summary>
		protected bool _addElement(XmlDocument xmlThis, XmlNode xnThis, string elname, string elval, string attname, string attval) {
			if(_addElement(xmlThis, xnThis, elname, elval))
				return(_addAttribute(xmlThis, xnThis.SelectSingleNode(elname), attname, attval));
			else
				return(false);
		}
		/// <summary>
		/// Adds an element and its attribute to a node of a document
		/// </summary>
		protected bool _addElement(XmlDocument xmlThis, XmlNode xnThis, string nodename, string elname, string elval, string attname, string attval) {
			if(_addElement(xmlThis, xnThis, nodename, "")) {
				XmlNode nde = xnThis.SelectSingleNode(nodename);
				if(_addElement(xmlThis, nde, elname, elval))
					return(_addAttribute(xmlThis, nde, attname, attval));
				else
					return(false);
			}
			else
				return(false);
		}
		/// <summary>
		/// A safe method of getting a string from a SqlDataReader column when the value could be null
		/// </summary>
		protected string _getString(SqlDataReader thisrdr, string thiscol) {
			int thisord = thisrdr.GetOrdinal(thiscol);
			return (thisrdr.IsDBNull(thisord))? "" : thisrdr.GetString(thisord);
		}
		/// <summary>
		/// A safe method of getting a boolean result from a SqlDataReader column when the value could be null
		/// </summary>
		protected string _getYesNo(SqlDataReader thisrdr, string thiscol) {
			int thisord = thisrdr.GetOrdinal(thiscol);
			return (thisrdr.IsDBNull(thisord))? "no" : "yes";
		}
	}
}
